package www.lagou.app;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import www.lagou.utils.DruidUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.TimeZone;

/**
 * 编程题1
 */
public class TransactionTest {
    /**
     * 实现卡号：1122334455向55443332211转账5000元的操作；
     * @return
     */
    public static boolean transaction() {
        boolean flag = false;
        QueryRunner qr = new QueryRunner(DruidUtils.DATASOURCE);
        String querySql = "select balance from account where card=?";
        Object[] param = new Object[]{"1122334455"};
        Double balance = null;
        try {
            balance = (Double) qr.query(querySql, new ScalarHandler<>(), param);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (balance < 5000) {
            System.out.println("余额不足");
            return flag;
        } else {
            Connection connection = null;
            PreparedStatement ps = null;
            try {
                //通过德鲁伊获取数据库连接
                connection = DruidUtils.getConnection();
                connection.setAutoCommit(false);
                //转入数据
                String inSql = "update account set balance=balance+5000 where card=?";
                ps = connection.prepareStatement(inSql);
                ps.setString(1, "55443332211");
                ps.executeUpdate();
                //转出数据
                String outSql = "update account set balance=balance-5000 where card=?";
                ps = connection.prepareStatement(outSql);
                ps.setString(1, "1122334455");
                ps.executeUpdate();
                //插入卡数据异动表SQL
                String transationSql="insert into TRANSACTION(cardid,tratype,tramoney,tradate) values(?,?,?,?)";
                ps=connection.prepareStatement(transationSql);

                SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                TimeZone.setDefault(TimeZone.getTimeZone("GMT+8"));
                //记录转出数据
                ps.setString(1,"1122334455");
                ps.setString(2,"转出");
                ps.setDouble(3,5000.0);
                ps.setTimestamp(4,new Timestamp(Timestamp.valueOf(sdf.format(System.currentTimeMillis())).getTime()));
                ps.executeUpdate();
                //记录转入数据
                ps.setString(1,"55443332211");
                ps.setString(2,"转入");
                ps.setDouble(3,5000.0);
                ps.setTimestamp(4,new Timestamp(Timestamp.valueOf(sdf.format(System.currentTimeMillis())).getTime()));
                ps.executeUpdate();
                connection.commit();
                flag=true;
            } catch (SQLException e) {
                e.printStackTrace();
                if(connection!=null){
                    try {
                        connection.rollback();
                    } catch (SQLException ex) {
                        ex.printStackTrace();
                    }
                }
            }finally {
                DbUtils.closeQuietly(ps);
                DbUtils.closeQuietly(connection);
            }
        }
        return flag;
    }

    public static void main(String[] args) {
        boolean b = transaction();
        if (b){
            System.out.println("转账成功！！！");
        }else{
            System.out.println("转账失败！！！");
        }
    }
}
